/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package admin;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.HashSet;
import java.util.Set;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Fifix
 */
public class exportBase extends HttpServlet{
    
     private Connection cnx;
    String path = "", loginBase, mdpBase, url, port;
    Statement stmt;
    
    
    private void connexion() {
        connexion("");     
    }
    
    private void connexion(String base){
        cnx = null;
        if(path.equals("")){
            path = this.getServletContext().getRealPath("config");
            loginBase = this.getServletContext().getInitParameter("login");
            mdpBase = this.getServletContext().getInitParameter("password");
            url = this.getServletContext().getInitParameter("host");
            port = this.getServletContext().getInitParameter("port");
        }
        
        try {
            Class.forName("com.mysql.jdbc.Driver");

            cnx = DriverManager.getConnection("jdbc:mysql://"+url+":"+port+"/"+base, loginBase, mdpBase);
            //cnx = DriverManager.getConnection("jdbc:mysql://localhost:3306/saisieNotes", "saisieNotes", "PasssaisieNotes");


            System.out.println("connexion à la base réussie");
        } catch (Exception e) {
            System.out.println("connexion ratée : " + e);
            System.exit(1);
        }
    }
    
    //TODO : Y'a un bug ici...Pas d'export de la structure
    //TODO : Remplir les tables.
    public String exporterBase(){
        try {

            
            stmt = cnx.createStatement();
            Calendar c = Calendar.getInstance();
            int annee = c.get(Calendar.YEAR);
            Connection cnxArchive = DriverManager.getConnection("jdbc:mysql://"+url+":"+port+"/archivewebnotes"+annee, loginBase, mdpBase);
            Statement stmtArchive = cnxArchive.createStatement();
            ResultSet rs = stmt.executeQuery("SHOW DATABASES LIKE 'archivewebnotes"+annee+"'"); 
            if( rs.next() ) {  //La database existe déjà, il faut la supprimer pour accueillir la nouvelle version...
                stmt.executeUpdate("DROP DATABASE archivewebnotes"+annee);
            }
            //Et on (re)crée la base...
            stmt.executeUpdate("CREATE DATABASE archivewebnotes"+annee); 
            cnx.close();
            connexion("webnotes");
            stmt = cnx.createStatement();
            //on récupère les métadonnées à partir de la connexion
            DatabaseMetaData dmd = cnx.getMetaData();
            //récupération des tables
            ResultSet tables = dmd.getTables(cnx.getCatalog(),null,"%",null);
            
            //Et on génère les tables...
            while(tables.next()){
               String sql = createTableSql(dmd, tables.getString("TABLE_NAME"));
                //On crée la table du côté archivewebnotes
                stmtArchive.executeUpdate(sql);
                stmtArchive.executeUpdate("INSERT INTO "+tables.getString("TABLE_NAME")+" SELECT * FROM webnotes."+tables.getString("TABLE_NAME"));
            }
            cnx.close();
            cnxArchive.close();
            
        } catch (SQLException ex) {
            ex.printStackTrace();
            return "ECHEC";
        }
        return "REUSSITE";
    }
    
    public String createTableSql( DatabaseMetaData metaData, String tableName ) throws SQLException {
        ResultSet columnsMetadata = metaData.getColumns( null, null, tableName, null );
        Set<String> pkNames = new HashSet<String>(3);

        try {
            ResultSet pkResultSet = metaData.getPrimaryKeys( null, null, tableName );
            
            
            while( pkResultSet.next() ) {
                //pkNames.add( pkResultSet.getString( 4 ) );
                
            }
        } catch( Exception e ) {
            e.printStackTrace();
        }

        StringBuffer sql = new StringBuffer();
        sql.append( "CREATE TABLE " + tableName + " (" );
        String delim = "";
        int i=1;
        while( columnsMetadata.next() ) {
            sql.append( delim );
            delim = ", ";
            String columnName = columnsMetadata.getString( 4 );
            String sqlTypename = columnsMetadata.getString(6);
            int columnSize = columnsMetadata.getInt( 7 );
            boolean nullsProhibited = "NO".equalsIgnoreCase( columnsMetadata.getString(18) );
            int precision = columnsMetadata.getInt( 9 );
            ResultSetMetaData rsmd = columnsMetadata.getMetaData();
            boolean estAutoIncr = rsmd.isAutoIncrement(i); i++;
            appendFieldCreationClause( sql, columnName, sqlTypename, columnSize, precision, nullsProhibited, pkNames.contains( columnName ), metaData.getIdentifierQuoteString(), columnsMetadata, estAutoIncr );
        }
        sql.append( ")" );
        return sql.toString();
    }

    public void appendFieldCreationClause( StringBuffer buffer, String columnName, String targetTypename, int columnSize, int precision, boolean nullsProhibited, boolean isPrimaryKey, String identifierQuoteString, ResultSet columnMetaData, boolean estAutoIncr ) {
        if( isPrimaryKey ) {
            targetTypename = "INT";
            precision = 0;
        }
        buffer.append( identifierQuoteString + columnName + identifierQuoteString + " " + targetTypename );
        if( columnSize > 0 && !targetTypename.equals("DATE")) {
            buffer.append( "(" + columnSize );
            if( precision > 0 ) {
                buffer.append( "," + precision );
            }
            buffer.append( ")" );
        }
        if( "id".equalsIgnoreCase( columnName ) ) {
            buffer.append( " UNIQUE" );
        }
        if( nullsProhibited ) {
            buffer.append( " NOT NULL" );
        }
        if( estAutoIncr ) {
            buffer.append( " AUTO_INCREMENT" );
        }
        if( isPrimaryKey ) {
            buffer.append( " PRIMARY KEY" );
        }
    }
   
   
    public void init(ServletConfig config) throws ServletException
    {
         super.init(config);
    }
    
	public void doGet(HttpServletRequest request,HttpServletResponse response) throws IOException, ServletException {
            connexion();
            PrintWriter out = response.getWriter();
            //response.setContentType( "text/xml" );
            //response.setHeader( "Cache-Control", "no-cache" );
            String action = request.getParameter("action");	
            if(action.equals("exporterBase")){
                out.print(exporterBase());
            }
            out.close();
	}
 
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws IOException, ServletException {
		doGet(request,response);
	}
    
}
